/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sistemPembayaranSPP;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import tools.koneksi;
import tools.koneksi1;

/**
 *
 * @author w7
 */
public class Kontrol1 {

    Connection conn;
    private static Kontrol1 tagihanKontrol;

    public Kontrol1(Connection koneksi1) {
        this.conn = koneksi1;
    }

    public static Kontrol1 getKoneksisms() throws Exception {
        koneksi1 k = new koneksi1();
        if (tagihanKontrol == null) {
            tagihanKontrol = new Kontrol1(k.getDBConnection());
        }
        return tagihanKontrol;

    }

    public void tambahDataTagihanSiswa(TagihanSiswa k) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "insert into "
                    + " TAGIHAN_SISWA (NIS, NAMAKELAS, NAMASISWA, JENISKELAMIN, NAMAORTU, NOTELEPON, TAHUNAJARAN, BIAYABULAN, TOTAL) values (?,?,?,?,?,?,?,?,?)";
            statement = (PreparedStatement) conn.prepareStatement(sql);
            statement.setString(1, k.getNIS());
            statement.setString(3, k.getNama());
            statement.setString(2, k.getNama_kelas());
            statement.setString(4, k.getJenis_kelamin());
            statement.setString(5, k.getNama_ortu());
            statement.setString(6, k.getNo_tlepn());
            statement.setString(7, k.getTahunAjaran());
            statement.setString(8, k.getBiayaBulan());
            statement.setString(9, k.getTotal());


            statement.executeUpdate();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }

    public void tambahDataPembayaran(Pembayaran k) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "insert into "
                    + " PEMBAYARAN (TANGGAL, NOTRANSAKSI, NIS, NAMAKELAS, DIBAYAR, KEMBALI) values (?,?,?,?,?,?)";
            statement = (PreparedStatement) conn.prepareStatement(sql);
            statement.setDate(1, new Date(k.getTanggalBayar().getTime()));
            statement.setString(2, k.getNoTransaksi());
            statement.setString(3, k.getNIS());
            statement.setString(4, k.getNamaKelas());
            statement.setDouble(5, k.getDibayar());
            statement.setDouble(6, k.getKembali());
//            statement.setDouble(7, k.getTotalBayar());

            statement.executeUpdate();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }
    
     public void tambhDataTagihanSiswa(TagihanSiswa k) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "insert into "
                    + " TAGIHAN_SISWA (NIS, NAMAKELAS, NAMASISWA, JENISKELAMIN, NAMAORTU, NOTELEPON, TAHUNAJARAN, BIAYABULAN, TOTAL) values (?,?,?,?,?,?,?,?,?)";
            statement = (PreparedStatement) conn.prepareStatement(sql);
            statement.setString(1, k.getNIS());
            statement.setString(3, k.getNama());
            statement.setString(2, k.getNama_kelas());
            statement.setString(4, k.getJenis_kelamin());
            statement.setString(5, k.getNama_ortu());
            statement.setString(6, k.getNo_tlepn());
            statement.setString(7, k.getTahunAjaran());
            statement.setString(8, k.getBiayaBulan());
            statement.setString(9, k.getTotal());


            statement.executeUpdate();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }

    public void smsGateway(TagihanSiswa k) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "INSERT INTO "+
	" outbox(Class,DestinationNumber,TextDecoded,SendingDateTime,RelativeValidity,SenderID,DeliveryReport,CreatorID) "+
	"VALUES('-1',?,'nis = "+k.getNIS()+" bayar',NOW(),-1,'','no','gammu')";
            statement = (PreparedStatement) conn.prepareStatement(sql);
            statement.setString(1, k.getNo_tlepn());
            System.out.println(k.getNo_tlepn());
            System.out.println(k.getNIS());
//            statement.setDouble(7, k.getTotalBayar());

            statement.executeUpdate();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }

    public TagihanSiswa getDataTagihanOtomatis(String NIS) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        TagihanSiswa k = null;
        try {
            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select NAMASISWA, NAMAKELAS, TAHUNAJARAN, BIAYABULAN ,total from TAGIHAN_SISWA where NIS=?");
            statement.setString(1, NIS);
            result = statement.executeQuery();
//            double  belumBayar =0;
            while (result.next()) {
                k = new TagihanSiswa();
                k.setNama(result.getString(1));
                k.setNama_kelas(result.getString(2));
                k.setTahunAjaran(result.getString(3));
                k.setBiayaBulan(result.getString(4));
                k.setTotal(result.getString(5));   
            }
        } catch (SQLException ex) {
            Logger.getLogger(Kontrol1.class.getName()).log(Level.SEVERE, null, ex);
        }
        return k;
    }

    public List<TagihanSiswa> getDataTagihanSiswa() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        List<TagihanSiswa> k = new ArrayList<TagihanSiswa>();
        try {
            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select NIS, NAMAKELAS, NAMASISWA, BIAYABULAN, TAHUNAJARAN from TAGIHAN_SISWA order by NIS");
            result = statement.executeQuery();

            //List<kategori> k = new ArrayList<kategori>();
            while (result.next()) {
                TagihanSiswa bar = new TagihanSiswa();
                bar.setNIS(result.getString(1));
                bar.setNama_kelas(result.getString(2));
                bar.setNama(result.getString(3));
//                bar.setTahunAjaran(result.getString(3));
                bar.setBiayaBulan(result.getString(4));
                bar.setTahunAjaran(result.getString(5));

                k.add(bar);
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(Kontrol1.class.getName()).log(Level.SEVERE, null, ex);
        }
        return k;
    }

    public List<TagihanSiswa> CariTagihanSiswa(String namaSiswa) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        List<TagihanSiswa> k = new ArrayList<TagihanSiswa>();
        try {
            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select NIS, NAMAKELAS, NAMASISWA, BIAYABULAN from TAGIHAN_SISWA where NAMASISWA like '%" + namaSiswa + "%' order by NIS");
            result = statement.executeQuery();
            TagihanSiswa bar = null;
            while (result.next()) {
                bar = new TagihanSiswa();
                bar.setNIS(result.getString(1));
                bar.setNama_kelas(result.getString(2));
                bar.setNama(result.getString(3));
                bar.setBiayaBulan(result.getString(4));
                k.add(bar);
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(Kontrol1.class.getName()).log(Level.SEVERE, null, ex);
        }
        return k;
    }

    public void updateTagihanSiswa(TagihanSiswa Uk) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "update TAGIHAN_SISWA set "
                    + " NIS=? NAMAKELAS=?, NAMASISWA, TAHUNAJARAN, BIAYABULAN =? where NIS=?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, Uk.getNIS());
            statement.setString(2, Uk.getNama_kelas());
            statement.setString(3, Uk.getNama());
            statement.setString(4, Uk.getTahunAjaran());
            statement.setString(5, Uk.getBiayaBulan());
            statement.executeUpdate();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }

    public List<Pembayaran> buatDataLaporan(int month) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        List<Pembayaran> k = new ArrayList<Pembayaran>();
        try {
            conn.setAutoCommit(false);
            statement = conn.prepareStatement(" select TANGGAL, NIS, NAMAKELAS, DIBAYAR, KEMBALI from PEMBAYARAN where TO_CHAR(TANGGAL, 'mm')=? order by NIS ");
            statement.setInt(1, month);
            result = statement.executeQuery();
  // Double saldoSesudah = 0.00;
            //List<kategori> k = new ArrayList<kategori>();
            while (result.next()) {
                Pembayaran bar = new Pembayaran();

                bar.setTanggalBayar(result.getDate(1));
                bar.setNIS(result.getString(2));
                bar.setNamaKelas(result.getString(3));
//                bar.setTotalBayar(result.getDouble(4));
                bar.setDibayar(result.getDouble(4));
                bar.setKembali(result.getDouble(5));
    //            Double saldoSebelum = result.getDouble(5)-result.getDouble(6);
      //          saldoSesudah=saldoSesudah + saldoSebelum;
        //        bar.setSaldo(saldoSesudah);
                k.add(bar);
          //      saldoSebelum = 0.00;
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(Kontrol1.class.getName()).log(Level.SEVERE, null, ex);
        }
        return k;
    }

     public void  updatePembayaran(Pembayaran uk) throws SQLException {
        PreparedStatement statement = null;
       // Pembayaran p= new Pembayaran();
//        Pembayaran p = new Pembayaran();
        try {
            conn.setAutoCommit(false);
            String sql = "update TAGIHAN_SISWA set "
                    + " total=?  where NIS=?";
            statement = conn.prepareStatement(sql);
            statement.setDouble(1, uk.getTotalBayar());
            statement.setString(2, uk.getNIS());
            statement.executeUpdate();
            
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException exception) {
                throw exception;
            }
        }
        
}

      public void hapusSiswa(TagihanSiswa hapusK) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "delete from TAGIHAN_SISWA "
                    + " where NIS='" + hapusK.getNIS() + "'";
            statement = conn.prepareStatement(sql);
            // statement.setString(1, hapusK.getKodeBarang());

            statement.executeUpdate();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }
}
